package com.sample.javabean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.MbBillForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * @version 1.0
 * @author Administrator
 * 慢病模型类，封装慢病校验方法
 */
public class MbBill {
public MbBill(){
	
}

public MbBill(String persNumber, String cardNo, String persName,
		String illname, String startday, String endday) {
	super();
	this.persNumber = persNumber;
	this.cardNo = cardNo;
	this.persName = persName;
	this.illname = illname;
	this.startday = startday;
	this.endday = endday;
}


	private String 	persNumber	;//	慢病证编号
	private String 	cardNo	;//	身份证号
	private String 	persName	;//	姓名
	private String 	illname	;//	疾病名称
	private String 	startday	;//	起始时间
	private String  endday ;//结束时间
	private JdbcUtils jdbcUtils =new JdbcUtils();
	
	/**
	 * 校验慢性病
	 * @param cardNo
	 * @param illNames
	 * @param treattime
	 * @return
	 */
	public boolean checkBill(String cardNo,String illNames,String treattime){
		boolean flag=false;
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs =null;
		try{
			conn =jdbcUtils.getConn();
	        stmt =conn.createStatement();
	        //构造SQL语句
	        String sql="select * from t_persinfo where cardNo='"+cardNo
	        		+"' and illName='"+illNames
	        		+"' and startday<='"+treattime+"' and endday>'"+treattime+"'";
			 rs =stmt.executeQuery(sql);
			 flag=rs.next()?true:false;
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			jdbcUtils.close(conn, stmt,rs);
			
		}
		return flag;
	}
	
	//查询慢性病全部信息
	public List<MbBill> findAll(){
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<MbBill> mbBillList =new ArrayList<MbBill>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			//构造SQL语句
			String sql="select * from t_mbBill where 1=1";
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				String persNumber=rs.getString("persNumber");
				String cardNo =rs.getString("cardNo");
				String persName =rs.getString("persName");
				String illname=rs.getString("illname");
				String startday =rs.getString("startday");
				String endday =rs.getString("endday");
				MbBill model=new MbBill(persNumber,cardNo,persName,illname,startday,endday);
				mbBillList.add(model);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return mbBillList;
		
	}
	
	//加载慢性病信息
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_mbbill where persNumber='"+this.persNumber+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.persNumber =rs.getString("persNumber");
				this.cardNo =rs.getString("cardNo");
				this.persName =rs.getString("persName");
				this.illname =rs.getString("illname");
				this.startday =rs.getString("startday");
				this.endday =rs.getString("endday");
				
			}
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SQLException("加载用户信息失败！");
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	
	/**
	 * 编辑慢性病信息
	 */
	public void editMbBill() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "update  t_mbbill set persNumber='"+this.persNumber+"',cardNo='"+this.cardNo+"',persName='"+this.persName+"',illname='"+this.illname+"',startday='"+this.startday+"',endday='"+this.endday+"' where persNumber='"+this.persNumber+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	
	/**
	 * 删除慢性病信息
	 */
	public void delMbBill() {

		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_mbbill  where persNumber='"+this.persNumber+"'";
			// 4:执行SQL并返回结果
			System.out.println("del mbbill");
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}

	}
	/**
	 * 添加慢性病信息
	 */
	public void add(){
		Connection conn =null;
		Statement stmt=null;
		try{
			//获得连接对象	
			 conn =jdbcUtils.getConn();
			//定义SQL
			String sql="insert into t_mbbill(persNumber,cardNo,persName,illname,startday,endday) values('"+this.persNumber+"','"+this.cardNo+"','"+this.persName+"','"+this.illname+"','"+this.startday+"','"+this.endday+"')";
			//Statement发送SQL语句给数据库
			 stmt =conn.createStatement();
			stmt.executeUpdate(sql);
		}catch (Exception e) {
			e.printStackTrace();
			
		}finally{
			jdbcUtils.close(conn, stmt);
			
		}
		
	}
	/**
	 * 分页显示慢性病信息
	 * @param page
	 * @param size
	 * @param form
	 * @return
	 */
	public Pager<MbBill> list(int page,int size,MbBillForm form) {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<MbBill> results=new Pager<MbBill>();
		results.setPage(page);
		results.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_mbbill where 1=1";
			//模糊查询身份证号
			if(form!=null&&form.getCardNo()!=null&&!"".equals(form.getCardNo())){
				selectSql +=" and cardNo like'%"+form.getCardNo()+"%'";
			}
			
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 results.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
			
				    String persNumber=rs.getString("persNumber");
					String cardNo =rs.getString("cardNo");
					String persName =rs.getString("persName");
					String illname=rs.getString("illname");
					String startday =rs.getString("startday");
					String endday =rs.getString("endday");
				  
				  
				
					MbBill model=new MbBill();
				  model.setPersNumber(persNumber);
				  model.setCardNo(cardNo);
				  model.setPersName(persName);
				  model.setIllname(illname);
				  model.setStartday(startday);
				  model.setEndday(endday);
				  results.getDataSets().add(model);
			 }
			 results.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return results;

	}
	
	
//get/set方法
	public String getPersNumber() {
		return persNumber;
	}
	public void setPersNumber(String persNumber) {
		this.persNumber = persNumber;
	}
	public String getCardNo() {
		return cardNo;
	}
	public void setCardNo(String cardNo) {
		this.cardNo = cardNo;
	}
	public String getPersName() {
		return persName;
	}
	public void setPersName(String persName) {
		this.persName = persName;
	}
	public String getIllname() {
		return illname;
	}
	public void setIllname(String illname) {
		this.illname = illname;
	}
	public String getStartday() {
		return startday;
	}
	public void setStartday(String startday) {
		this.startday = startday;
	}
	public String getEndday() {
		return endday;
	}
	public void setEndday(String endday) {
		this.endday = endday;
	}
	public JdbcUtils getJdbcUtils() {
		return jdbcUtils;
	}
	public void setJdbcUtils(JdbcUtils jdbcUtils) {
		this.jdbcUtils = jdbcUtils;
	}

	
	
	
	
}